﻿CREATE proc [dbo].[sp_getWxDaliyData] (@startDate datetime)
as 
begin 
  declare @endDate datetime 
  select @endDate = GETDATE()
      
  if exists(select * from tempdb.dbo.sysobjects where Name  like N'%#dateTable%' and type='U')
  drop table #dateTable 
  
  create table #dateTable ([date] varchar(10))        
  
  declare @dtTemp as datetime 
  select @dtTemp = @startDate   
  while (@dtTemp <= @endDate)
  begin 
	insert into #dateTable([date]) select Convert(varchar,@dtTemp,23)
	select @dtTemp =DATEADD(day,1, @dtTemp)
  end 
  
  select a.[date],coalesce(e.bindCnt,0) as bindCnt,coalesce(b.apptCnt,0) as apptCnt,coalesce(b.apptSum,0.0) as apptSum,
		coalesce(c.regCnt,0) as regCnt,coalesce(c.regSum,0.0) as regSum,
		coalesce(d.payCnt,0) as payCnt,coalesce(d.paySum ,0.0) as paySum ,
		coalesce(b.apptSum,0.0) + coalesce(c.regSum,0.0) + coalesce(d.paySum ,0.0) as daliyMoney
  from #dateTable a left join 
  (
  --预约
  select CONVERT(varchar,CreateTime,23)as [date],COUNT(*) as apptCnt,SUM(coalesce(SumFee,0)) as apptSum from ApptOrder 
  where OrderType = 0 and PayState = 1 and OrderStatus in(21, 23) and WxUserId <>13 and 
  CreateTime between  @startDate and  @endDate
  group by CONVERT(varchar,CreateTime,23)
  ) b on a.date = b.date   
  left join 
  (
  --挂号
  select CONVERT(varchar,CreateTime,23)as [date],COUNT(*) as regCnt,SUM(coalesce(SumFee,0)) as regSum from ApptOrder 
  where OrderType = 2 and PayState = 1 and OrderStatus = 21 and WxUserId <>13 and 
  CreateTime between  @startDate and  @endDate
  group by CONVERT(varchar,CreateTime,23)
  ) c on a.date = c.date 
  left join 
  (
  --pay
  select CONVERT(varchar,CreateTime,23)as [date],COUNT(*) as payCnt,SUM(coalesce(PrescMoney,0)) as paySum from BillOrder 
  where PayState = 1 and OrderStatus = 8 and WxUserId <>13 and 
  CreateTime between  @startDate and  @endDate
  group by CONVERT(varchar,CreateTime,23)
  ) d on a.date = d.date
  left join  
  (
  --bindCnt 
	select CONVERT(varchar,RecCreateDt,23) as [date] ,COUNT(*) as bindCnt 
	from HospCard where RecCreateDt between  @startDate and  @endDate 
	GROUP by CONVERT(varchar,RecCreateDt,23) 
  ) e on a.date = e.date
   
    
  if exists(select * from tempdb..sysobjects where  name  like N'%#dateTable%' and type='U')
  drop table #dateTable 
  
end 